﻿	SELECT 
	C.YR, 
	C.MN, 
	SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
	SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
	IPTV_IN-IPTV_OUT IPTV_NET,  
	SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
	SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
	HSIA_IN-HSIA_OUT HSIA_NET,  
	SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
	SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
	VOIP_IN-VOIP_OUT VOIP_NET, 
	IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
	'REGION' ROLLUP_TYPE,  
	REGION,
	PRODUCT, 
	LOCATION ,
	DWELLING
	FROM	
	(  
		SELECT	C.DIRECTION, C.START_DT, 
		CASE UPPER(C.DWELLING_TYPE_U)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(C.PRODUCT, 'ALL') PRODUCT, 
		COALESCE(CASE C.NATIONAL_ID WHEN 'YES' THEN 'NATIONAL'  WHEN 'NO' THEN 'REGIONAL' END, 'BOTH') LOCATION , 
		COALESCE(C.REGION ,'ALL') REGION
		FROM	
		( -- C --ACC_BASE_DATA
			SELECT 
			 REGION_DESC_U REGION
			,MARKET_AREA_U MARKET
			,STATE_U STATE_CD
			,AM_U AREA_MGR
			,AE_ASSIGNED_U ACCT_MGR
			,PORTFOLIO_NAME_U PORTFOLIO
			,STATE_U||PROPERTY_ID_U TRAC_ID
			,MONTH(POSTDATE_U) MN
			,YEAR(POSTDATE_U) YR
			,CASE 
			 WHEN INDEX(PRODUCT_NM_U,'VOICE') GT 0 THEN 'VOIP' 
			 WHEN INDEX(PRODUCT_NM_U,'HSIA') GT 0 THEN 'HSIA' 
			 ELSE 'IPTV' 
			 END PRODUCT
			,CASE PRODUCT_ACTION_CD_U
			 WHEN 'O' THEN 'OUT'
			 WHEN 'I' THEN 'IN'  
			 END DIRECTION
			,PROD_QTY_U 
			,CASE NATIONAL_IND_U
			 WHEN 'Y' THEN 'YES'
			 WHEN 'N' THEN 'NO'
			 END NATIONAL_ID
			,DWELLING_TYPE_U
			,PROPERTY_NAME_U
			,POSTDATE_U START_DT
			 FROM
			( -- TOT 		
				SELECT  
				REGION AS REGION_DESC_U
				,MARKET_AREA  AS MARKET_AREA_U
				,AM  AS AM_U
				,AE_ASSIGNED  AS AE_ASSIGNED_U
				,PORTFOLIO_NAME  AS PORTFOLIO_NAME_U
				,STATE AS STATE_U
				,LEGS.PROPERTY_ID AS PROPERTY_ID_U
				,PROPERTY_NAME  AS PROPERTY_NAME_U
				,NATIONAL_IND_U
				,DWELLING_TYPE_U
				,POSTDATE  AS POSTDATE_U
				,PRODUCT_NM           AS PRODUCT_NM_U       
				,PRODUCT_ACTION_CD    AS PRODUCT_ACTION_CD_U  
				,PROD_QTY AS PROD_QTY_U
				FROM
				( -- LEGS			
					SELECT  
					 PROPERTY_ID 
					,REGION
					,PROPERTY_NAME
					,NATIONAL_IND  AS  NATIONAL_IND_U
					,DWELLING_TYPE  AS DWELLING_TYPE_U
					,AM
					,AE_ASSIGNED
					,PORTFOLIO_NAME
					,MARKET_AREA
					,POSTDATE
					,PRODUCT_ID   
					,PRODUCT_ACTION_CD             
					,PROD_QTY  
					,STATE   
					FROM
					( -- CHK
						SELECT 
						S.PROPERTY_ID 
						,SC.REGION
						,SC.PROPERTY_NAME
						,SC.NATIONAL_IND
						,SC.DWELLING_TYPE
						,SC.AM
						,SC.AE_ASSIGNED
						,SC.PORTFOLIO_NAME
						,SC.MARKET_AREA
						,E.POSTDATE
						,E.STATE
						FROM 
						SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
						S
						,
						SMARTMOVES.TXSM101_SO_SCORE 	
						SC
  						,
						( -- E
							SELECT 
							POSTDATE
							,STATE
							FROM
							( -- BASE
								SELECT	
								POSTDATE
								,C.TERRITORY_CD AS STATE
								FROM  
								ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
								C  
								,
								( -- S	
									SELECT 
									H205.EVENT_POSTED_DT    			POSTDATE
									FROM
									ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT 
									H205
								) 
								S
								WHERE 
								C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
								AND C.ZIP_CD <> ' '
								AND C.ADDRESS_USAGE_CD = 'S' 
							) 
							BASE
							GROUP BY 1,2
						) 
						E
						WHERE 
						TRIM(S.ADDRESS) = TRIM(E.ADDRESS)
						AND S.ZIP = E.ZIP_CD
						AND S.PROPERTY_ID = SC.PROPERTY_ID
						AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) 
					) 
					CHK
					LEFT OUTER JOIN 
					( -- ACT				
						SELECT 
						 D.PRODUCT_ID                    
						,CASE WHEN INWARD_OUTWARD_CD = 1 THEN 'I' ELSE 'O' END AS PRODUCT_ACTION_CD             
						,SUM(D.PRODUCT_QTY) PROD_QTY                  
						FROM 
						ENTERPRISE_RETAIL_VIEWS.VCCH207_SVC_ORD_PROD_EVENT  
						D
						WHERE  
						D.DATA_SOURCE_CD IN (150)
						AND D.INWARD_OUTWARD_CD  IN (1,4)
						AND D.PRODUCT_ID > 0
						GROUP BY 1,2
					) 
					ACT
					ON CHK.EVENT_ID = ACT.EVENT_ID
				) 
				LEGS
				WHERE PRODUCT_ACTION_CD IN ('I','O')
				GROUP BY 1,2,3,4,5,6,7,8,9,10
			) 
			TOT
		/*  ADDED PER  MIST  131152: Determine/Develop Process for CLLI-DA Tracking for ACC- Uverse    */
			GROUP BY 1,2,3,4,5,6,7,8,9,10
		) 
		C	--ACC_BASE_DATA
		ON	C.TRAC_ID=B.TRAC_ID 
	) 
	B 
	WHERE MN='11' AND YR='2010'
	GROUP	BY CUBE  (REGION, PRODUCT, LOCATION, DWELLING), YR, MN 
	ORDER BY REGION, PRODUCT, LOCATION,  DWELLING, YR, MN 
	;
